import fs from 'fs';
import path from 'path';
import Jimp from 'jimp';
import axios from 'axios';
import { connection } from '../app/database/mysql'
import { getUserById } from '../user/user.service';
import { VideoModel } from './videos.model';
import { APP_URL, STATIC_PATH } from '../app/app.config';

/**
 * 获取
 */
export const getVideos = async (sortNum: number, pageSize: number, pageNo: number, status: number) => {
  // 准备查询
  let statement = `
    SELECT SQL_CALC_FOUND_ROWS * FROM videos
  `;
  let where = []
  if (sortNum >= 0) {
    where.push(`sortNum = ` + sortNum)
  }
  if (status > 0) {
    where.push(`status = ` + status)
  }
  if (where.length > 0) {
    statement += ` where `
    for (let i = 0; i < where.length; i++) {
      statement += where[i]
      if (i < where.length - 1) {
        statement += ` and `
      }
    }
  }
  statement += ` ORDER by sortNum DESC, id DESC limit ` + (pageNo > 0 ? (pageNo - 1) * pageSize : 0) + `,` + pageSize

  // 执行查询
  const [data] = await connection.promise().query(statement);

  let totalStatement = `SELECT FOUND_ROWS() as total`
  const [result] = await connection.promise().query(totalStatement)
  let totalCount = result[0].total
  let totalPage = (totalCount % pageSize) > 0 ? parseInt(`${totalCount / pageSize}`) + 1 : totalCount / pageSize

  for (let item of data as any) {
    const user = await getUserById(item.user_id);
    item.user = user 
    if (item.url.indexOf('wxv')>=0){
      const url = 'https://mp.weixin.qq.com/mp/videoplayer?action=get_mp_video_play_url&preview=0&__biz=Mzg3NTIxNzg3OQ==&mid=2247486954&idx=3&vid=' + item.url;
      const resp = axios.get(url)
      let videos = (await resp).data.url_info
      if (videos && videos.length > 0) {
        item.url = videos[0].url
      }
    }
  }

  let res = {
    data,
    totalPage,
    totalCount
  }
  // 提供数据
  return res;
};

/**
 * 按 user ID 获取内容
 */
export const getVideosById = async (userId: number, pageSize: number, pageNo: number) => {
  // 准备查询
  let statement = `
    SELECT SQL_CALC_FOUND_ROWS id, url, image_id, content_zh, favorite_count FROM videos
  `;
  let where = []
  if (userId >= 0) {
    where.push(`user_id = ` + userId)
  }
  if (where.length > 0) {
    statement += ` where `
    for (let i = 0; i < where.length; i++) {
      statement += where[i]
      if (i < where.length - 1) {
        statement += ` and `
      }
    }
  }
  statement += ` ORDER by sortNum DESC, id DESC limit ` + (pageNo > 0 ? (pageNo - 1) * pageSize : 0) + `,` + pageSize

  // 执行查询
  const [data] = await connection.promise().query(statement);

  let totalStatement = `SELECT FOUND_ROWS() as total`
  const [result] = await connection.promise().query(totalStatement)
  let totalCount = result[0].total
  let totalPage = (totalCount % pageSize) > 0 ? parseInt(`${totalCount / pageSize}`) + 1 : totalCount / pageSize
  
  for (let item of data as any) {
    if (item.url.indexOf('wxv') >= 0) {
      const url = 'https://mp.weixin.qq.com/mp/videoplayer?action=get_mp_video_play_url&preview=0&__biz=Mzg3NTIxNzg3OQ==&mid=2247486954&idx=3&vid=' + item.url;
      const resp = axios.get(url)
      let videos = (await resp).data.url_info
      if (videos && videos.length > 0) {
        item.url = videos[0].url
      }
    }
    if (item.image_id && item.image_id > 0) {
      let cover = await findFileById(item.image_id, 0, 0);
      item.cover = cover[0];
    }
  }
  
  let res = {
    data,
    totalPage,
    totalCount
  }

  // 提供数据
  return res;
}

/**
 * 按 video ID 获取内容
 */
export const getVideoById = async (postId: number) => {
  // 准备查询
  const statement = `
    SELECT * FROM videos
    WHERE id = ?
  `;

  // 执行查询
  const [data] = await connection.promise().query(statement, postId);

  let video = data[0] as any
  if (video) {
    let images = await findFileById(0, video.id, 23);
    video.images = images;
  }

  // 提供数据
  return video;
}

/**
 * 按关键字获取（模糊查询）
 */
export const byKeyWordSelect = async (keyWord) => {
  // 准备查询
  const statement = `
    SELECT * FROM videos
    WHERE (user_id = '${keyWord}' or content_zh LIKE '%${keyWord}%')
  `;

  // 执行查询
  const [data] = await connection.promise().query(statement, keyWord);

  // 提供数据
  return data;
}

/**
 * 统计个人收点赞
 */
export const favoriteCount = async (userId: number) => {
  // 准备查询
  const statement = `
    SELECT SUM(favorite_count) AS FC FROM videos WHERE user_id = ?
  `;

  // 执行查询
  const [data] = await connection.promise().query(statement, userId);

  // 提供数据
  return data;
}

/**
 * 创建
 */
export const addVideos = async (videosModel: VideoModel) => {
  // 准备查询
  const statement = `
    INSERT INTO videos
    SET ?
  `;

  // 执行查询
  const [data] = await connection.promise().query(statement, videosModel);

  // 提供数据
  return data;
};

/**
 * 更新
 */
export const updateVideos = async (videosModel: VideoModel, postId: number) => {
  // 准备查询
  const statement = `
    UPDATE videos
    SET ?
    WHERE id = ?
  `;

  // 执行查询
  const [data] = await connection.promise().query(statement, [videosModel, postId]);

  // 提供数据
  return data;
};

/**
 * 统计数量
 */
export const countFavorites = async (count: number, id:number) => {
  // 准备查询
  const statement = `
    update videos
     set favorite_count = favorite_count + (?) where id = ?
  `;

  // 执行查询
  const [data] = await connection.promise().query(statement, [count, id]);

  // 提供数据
  return data;
};

/**
 * 存储文件信息
 */
export const createFile = async (
  file: VideoModel
) => {
  // 准备查询
  const statement = `
    INSERT INTO resource
    SET ?
  `;

  // 执行查询
  const [data] = await connection.promise().query(statement, file);

  // 提供数据
  return data;
}

/**
 * 按 ID 查找文件
 */
export const findFileById = async (fileId: number, targetId: number, type: number, ...size: any) => {
  // 准备查询
  let sql = 'SELECT * FROM resource ';
  let and = false;
  if (fileId > 0) {
    sql += " where id = " + fileId;
    and = true;
  }
  if (targetId > 0) {
    sql += (and ? " and " : " where ") + " target_id = " + targetId;
    and = true;
  }
  if (type > 0) {
    sql += (and ? " and " : " where ") + " target_type = " + type;
  }

  // 执行查询
  const [data] = await connection.promise().query(sql);
  for (let item of data as any) {
    if (size && size.length > 0) {
      item.fullUrl = APP_URL + item.url + '/resized/' + item.filename + "-" + size[0]
    } else {
      item.fullUrl = APP_URL + item.url + item.filename
    }
  }

  // 提供数据
  return data as any;
}

/**
 * 删除内容文件
 */
export const deleteFileById = async (file: VideoModel) => {
  if (!file.id || file.id <= 0) return
  const statement = `delete from resource where id = ?`;
  const [data] = await connection.promise().query(statement, file.id);
  if ((data as any).affectedRows > 0) {
    let path = STATIC_PATH + file.url + file.filename

    try {
      fs.unlinkSync(path);
    } catch (error) {
      console.log("fileDeleteErr:", error);
    }
  };

}

/**
 * 调整图像尺寸
 */
export const imageResizer = async (
  image: Jimp,
  file: Express.Multer.File
) => {
  // 图像尺寸
  const { imageSize } = image['_exif'];

  // 文件路径
  const filePath = path.join(file.destination, 'resized/', file.filename);

  // 大尺寸
  if (imageSize.width > 1080) {
    image
      .resize(1080, Jimp.AUTO)
      .quality(85)
      .write(`${filePath}-lerge`);
  }

  // 中等尺寸
  if (imageSize.width > 440) {
    image
      .resize(440, Jimp.AUTO)
      .quality(95)
      .write(`${filePath}-medium`);
  }

  // 缩略图
  if (imageSize.width > 320) {
    image
      .resize(320, Jimp.AUTO)
      .quality(85)
      .write(`${filePath}-thumbnail`);
  }
}

/**
 * 删除内容
 */
export const deleteVideo = async (postId: number) => {
  // 准备查询
  const statement = `
    DELETE FROM videos
    WHERE id = ?
  `;

  // 执行查询
  const [data] = await connection.promise().query(statement, postId);

  // 提供数据
  return data;
}

/**
 * 状态
 */
export const statusChange = async (postId: number, post: any) => {
  // 准备数据
  const statement = `
    UPDATE videos
      SET ?
      WHERE id = ?
  `;

  // 执行查询
  const [data] = await connection.promise().query(statement, [post, postId]);

  // 提供数据
  return data;
};
